<?php

/* If no headers was sent: main call */
if (!headers_sent())
{
    $excel = True;

    require('database.php');
    require('auth.php');
    require('core.php');

    // Verifica se esta autenticado com pelo menos nivel 3
    validaPermissao(3);

    // Define file type
    header("Content-type: application/vnd.ms-excel");

    // Define file name and to be downloaded
    header("Content-Disposition: attachment; filename=\"Locations.xls\"");
    
}

/* Gets time period */
$from = (@$_GET['from']? strtotime($_GET['from']) : inicio_ultima_quinzena());
$to = (@$_GET['to']? strtotime($_GET['to']) : fim_ultima_quinzena());

if ($to)
	$to = min($to, $_SERVER['REQUEST_TIME']);

/* Gets query from DB */
$resultado = query_bd("SELECT *, locations.id as locid FROM locations, contacts WHERE " .
	"operating=TRUE AND locations.locationcontact=contacts.id " .
	(isset($_GET['search'])? "AND contacts.name LIKE '%{$_GET['search']}%'" : "") .
	" ORDER BY contacts.name");

// Mostra uma tabela com todas as locacoes, e alguns dados
echo "<table id=locations>";

if (!isset($excel))
    echo "<caption><em>", mysql_affected_rows(), " Operating locations, with data analisys" .
    ($from? " from " . date("M j Y", $from) : ""),
    ($to? " until " . date("M j Y", $to) : ""),
    "</em></caption>";

echo "<tr><th>Name<br>Ticket range<th>Manager payrate<th>Valet payrate<th>Insurance tax",
	"<th>Void<br>Validated<br>Hosted<br>Paid<th>Labor<th>Charge<br>Insurance<br>Tip<th>Balance<th>",
	"Income per car<th>Expense per car<th>Balance per car<th>Insurance<br>Accident<hr>Balance";

// Initialize totals
$TOTAL_LABOR_EXP = 0;
$TOTAL_VOID = 0;
$TOTAL_VALIDATED = 0;
$TOTAL_HOSTED = 0;
$TOTAL_PAID = 0;
$TOTAL_CARS = 0;
$TOTAL_CHARGE = 0;
$TOTAL_INSURANCE = 0;
$TOTAL_TIP = 0;
$TOTAL_INCOME = 0;
$TOTAL_LABOR = 0;
$TOTAL_ACCIDENT = 0;

while ($location = mysql_fetch_array($resultado))
{
	flush();	/* Manda o que ja tem em buffer para o cliente */

    /*    
        NOTE: If you accumulate data from reports in a query which finds every
        cars_paid for reports you will probably accumulate reports twice as
        there is a N to one relation. So, report data gattering must be in a
        separate query
    */
	// reports data gattering 
	$reports = query_fetch("SELECT SUM(TIME_TO_SEC(labor)) as totallabor, -SUM(labor_expense) AS totallabor_exp FROM reports WHERE " .
	"special_event=FALSE AND state = " . ACCEPTED .
	" AND location_event_id={$location['locid']} " .
	($from? " AND UNIX_TIMESTAMP(reports.reportdate) >= {$from}" : "") .
	($to? " AND UNIX_TIMESTAMP(reports.reportdate) <= {$to}" : "") );
	$total_labor = $reports['totallabor'];
	$total_labor_exp = $reports['totallabor_exp'];
	unset($reports);

	// cars_paid data gattering
	$location_info = query_fetch(
	"SELECT SUM(cars_paid.cars) as cars, SUM(cars_paid.charge*cars_paid.cars) as charge,  " .
	"SUM(cars_paid.cars*reports.insurance_tax) as insurance " .
	"FROM cars_paid, reports WHERE " .
	"cars_paid.report_id=reports.id AND reports.special_event=FALSE AND reports.state = " . ACCEPTED .
	" AND reports.location_event_id={$location['locid']} " .
	($from? " AND UNIX_TIMESTAMP(reports.reportdate) >= {$from}" : "") .
	($to? " AND UNIX_TIMESTAMP(reports.reportdate) <= {$to}" : "") );
	$total_insurance = $location_info['insurance'];
	$total_paid = $location_info['cars'];
	$total_charge = $location_info['charge'];

	// Soma tips
	$location_info = query_fetch(
	"SELECT SUM(cars_void) as void, SUM(cars_validated) as validated, SUM(cars_hosted) as hosted, " .
	"SUM(tip) as tip, SUM(labor_expense) as labor_exp, " .
	"SUM((cars_void+cars_validated+cars_hosted)*insurance_tax) as insurance FROM reports WHERE " .
	"special_event=FALSE AND location_event_id={$location['locid']}  AND state = " . ACCEPTED .
	($from? " AND UNIX_TIMESTAMP(reportdate) >= {$from}" : "") .
	($to? " AND UNIX_TIMESTAMP(reportdate) <= {$to}" : "") );
	$total_tip = $location_info['tip'];
	$total_hosted = $location_info['hosted'];
	$total_validated = $location_info['validated'];
	$total_void = $location_info['void'];
	$total_insurance += $location_info['insurance'];

	//Calcula acidente
	$accidents = query_fetch("SELECT SUM(value) AS accident FROM duedates WHERE " .
	"category=" . ACCIDENT . " AND value<0 AND references_to=" . LOCATION .
	" AND reference_id={$location['locid']}" .
	($from? " AND UNIX_TIMESTAMP(payday) >= {$from}" : "") .
	($to? " AND UNIX_TIMESTAMP(payday) <= {$to}" : "") );
	$total_accident = $accidents['accident'];

	// soma
	$total_cars = $total_hosted + $total_validated + $total_void + $total_paid;
	$total_income = $total_charge + $total_tip;

	echo "<tr>";
	echo "<td><a href='"  . dirname($_SERVER['HTTP_REFERER']) . "/location.php?id={$location['locid']}'>" . sanitizeHTML($location['name']) . "</a>";

	/* busca por tickets ranges e mostra */
    $ticket_range_result = query_bd("SELECT ticket_start, ticket_end FROM ticket_ranges WHERE location_id={$location['locid']}");
    while ($ticket_range = mysql_fetch_array($ticket_range_result))
    {
        echo "<br>",  sprintf("%06d", $ticket_range['ticket_start']), "-",
            sprintf("%06d", $ticket_range['ticket_end']), "(", ($ticket_range['ticket_end']-$ticket_range['ticket_start']), ")";
    }
    unset($ticket_range_result);

	echo "<td>&#36;{$location['manager_payrate']}";
	echo "<td>&#36;{$location['valet_payrate']}";
	echo "<td>&#36;{$location['insurance_tax']}";
	echo "<td>", number_format($total_void), "<br>", number_format($total_validated), 
         "<br>", number_format($total_hosted), "<br>", number_format($total_paid), 
         "<hr>", number_format($total_cars);
	echo "<td>", toTime($total_labor), "<hr>", show_money($total_labor_exp);
	echo "<td>", show_money($total_charge), "<br>", show_money($total_insurance),
		"<br>", show_money($total_tip), "<hr>", show_money($total_income);
	$balance = $total_income + $total_labor_exp;
	echo "<td>", show_money($balance);

	$inc_car = ($total_cars > 0? $total_income/$total_cars : 0);
	$exp_car = ($total_cars > 0? $total_labor_exp/$total_cars : 0);

	echo "<td>", show_money($inc_car);
	echo "<td>", show_money(-$exp_car);
	echo "<td>", show_money($inc_car - $exp_car);
	echo "<td>", show_money($total_insurance), "<br>", show_money($total_accident),
	"<hr>", show_money($total_insurance - $total_accident);

	// Soma totais
	$TOTAL_LABOR_EXP += $total_labor_exp;
	$TOTAL_VOID += $total_void;
	$TOTAL_VALIDATED += $total_validated;
	$TOTAL_HOSTED += $total_hosted;
	$TOTAL_PAID += $total_paid;
	$TOTAL_CARS += $total_cars;
	$TOTAL_CHARGE += $total_charge;
	$TOTAL_INSURANCE += $total_insurance;
	$TOTAL_TIP += $total_tip;
	$TOTAL_INCOME += $total_income;
	$TOTAL_LABOR += $total_labor;
	$TOTAL_ACCIDENT += $total_accident;

	unset($location);
}
unset($resultado);

//tira conclusoes gerais
$TOTAL_BALANCE = $TOTAL_INCOME + $TOTAL_LABOR_EXP;
$EXPENSE_CAR = ($TOTAL_CARS> 0? $TOTAL_LABOR_EXP / $TOTAL_CARS : 0);
$INCOME_CAR = ($TOTAL_CARS> 0? $TOTAL_INCOME / $TOTAL_CARS : 0);

// Imprime conclusoes gerais
echo "<tr><th><th><th><th><th>", ($TOTAL_VOID? number_format($TOTAL_VOID) : ZERO), 
    "<br>", ($TOTAL_VALIDATED?  number_format($TOTAL_VALIDATED) : ZERO), 
    "<br>", ($TOTAL_HOSTED?  number_format($TOTAL_HOSTED) : ZERO),
	"<br>", ($TOTAL_PAID?  number_format($TOTAL_PAID) : ZERO), 
    "<hr>", ($TOTAL_CARS?  number_format($TOTAL_CARS) : ZERO),
	"<th>", toTime($TOTAL_LABOR) . "<hr>", show_money($TOTAL_LABOR_EXP), "<th>", show_money($TOTAL_CHARGE),
	"<br>", show_money($TOTAL_INSURANCE), "<br>", show_money($TOTAL_TIP), "<hr>",
	show_money($TOTAL_INCOME) . "<th>", show_money($TOTAL_BALANCE), "<th>", show_money($INCOME_CAR),
	"<th>", show_money(-$EXPENSE_CAR), "<th>", show_money($INCOME_CAR - $EXPENSE_CAR),
	"<th>", show_money($TOTAL_INSURANCE), "<br>", show_money($TOTAL_ACCIDENT),
	"<hr>", show_money($TOTAL_INSURANCE-$TOTAL_ACCIDENT);

echo "</table>";

?>

